#-*- coding: utf-8 -*-

import csv
import sqlite3
from GetData import GetData
from GetStatistics import GetStatistics
import re

class WriteData():
    #Constructor
    def __init__(self):
        self.data = GetData()
        self.stat = GetStatistics()
        pass
#
#---------------> CSV
#
    #write csv and create the file
    #@ year - year to write
    def write_on_csv(self, year):
        print "Year : 200{0}".format(year)
        self.file = open('DataOf200{0}.csv'.format(year),"wb")
        self.csvwriter = csv.writer(self.file)
        self.write_lists_on_csv(year)
        self.write_estatistics_csv(year)
        self.stat.cursor.close()
        self.file.close

    #write lists on csv file
    #@ year - year to write
    def write_lists_on_csv(self, year):
        self.stat = GetStatistics()
        self.csvwriter.writerow(['LISTS... ' ])
        self.csvwriter.writerow(['Institutions... ' ])
        list = self.stat.get_lists('institution', year)
        self.write_list(list)
        print "institutions DONE!"

        self.stat = GetStatistics()
        self.csvwriter.writerow(['Establishments... ' ]) 
        list = self.stat.get_lists('establishments', 0)
        self.write_list(list)
        print "Establishments DONE!"

        self.stat = GetStatistics()
        self.csvwriter.writerow(['Holders of Degree... ' ])
        list = self.stat.get_lists('teacher', 0)
        self.write_list(list)
        print "Holders List DONE!"
        pass

    #write the lists on the file
    #@ list - list to write
    def write_list(self, list):
        for x in list:
            self.csvwriter.writerow([x.encode('utf8')])

    #write the dictionaries on csv
    #@param dict - list to write
    #@param bool - if as or not 2 elements on key element
    def write_dict(self, dict, bool):
        if bool == True:
            for x, y in dict.iteritems():
                self.csvwriter.writerow([x[0].encode('utf8') + ", " + x[1].encode('utf8') + " - " + str(y)])
        else:
            for x, y in dict.iteritems():
                self.csvwriter.writerow([x.encode('utf8') + " - " + str(y)])

    #write esstatistics on csv
    #@ year - year to search
    def write_estatistics_csv(self, year):
        self.stat = GetStatistics()
        print "Writing Estatistis. Please wait, can take a wile..."
        self.csvwriter.writerow(['ESTATISTICS.....' ])
        self.csvwriter.writerow(['total number of teachers in the higher education system per year... ' ])
        list = self.stat.get_num_teachers_year(year)
        self.csvwriter.writerow([list])

        self.stat = GetStatistics()
        self.csvwriter.writerow(['total number of teachers per institution and per year' ])
        list = self.stat.get_num_teachers_anywhere_per_year('institution', 'teachers_cards', year)
        self.write_dict(list, False)

        self.stat = GetStatistics()
        self.csvwriter.writerow(['total number of teachers per establishment and per year' ])
        list = self.stat.get_num_teachers_anywhere_per_year('establishments', 'teachers_cards', year)
        self.write_dict(list, False)

        self.stat = GetStatistics()
        self.csvwriter.writerow(['total number of teachers per degree and per year' ])
        list = self.stat.get_num_teachers_anywhere_per_year('degree', 'curse_cards', year)
        self.write_dict(list, False)

        self.stat = GetStatistics()
        self.csvwriter.writerow(['total number of teachers per degree, per establishment and per year' ])
        list = self.stat.get_num_teachers_degree_establishment_year(year)
        self.write_dict(list, True)
        print "Estatistics DONE!"
        
#### -------> DATABASE
    #write on database
    def write_on_database(self):
        self.listNames = []
        self.listCodes = []
        self.listCategorys = []
        self.listRegimes = []
        self.listDegrees = []
        self.listCurses = []
        self.connect_to_database()
        self.create_tables()
        for i in range(0, 10):
            self.fill_institutions_table(i)
            self.fill_establishment_table(i)
            self.fill_categories_table(i)
            self.fill_rest(i)
        self.cursor.close()
        print "done!"
        pass

    #Connect to the file .db or create if not exists
    def connect_to_database(self):
        self.connect = sqlite3.connect('rebidesDB.db')
        self.cursor = self.connect.cursor()
        pass

    #Create all the tables
    def create_tables(self):
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Degrees
                            (designation TEXT PRIMARY KEY)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Curses
                            (designation TEXT PRIMARY KEY)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Category
                            (designation TEXT PRIMARY KEY)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Regimes
                            (designation TEXT PRIMARY KEY)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Establishments
                            (designation TEXT PRIMARY KEY,
                            code INTEGER)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Institutions
                            (designation TEXT PRIMARY KEY,
                            code INTEGER)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Teachers
                            (name TEXT PRIMARY KEY,
                            code INTEGER UNIQUE)""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Curse_Cards
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            year INTEGRER,
                            teacher REFERENCES Teachers(name),
                            degree REFERENCES Degrees(designation),
                            curse REFERENCES Curses(designation))""")
        
        self.cursor.execute("""CREATE TABLE IF NOT EXISTS Teachers_Cards
                            (id INTEGER PRIMARY KEY AUTOINCREMENT,
                            teacher REFERENCES Teachers(name),
                            year INTEGRER,
                            institution REFERENCES Institutions(designation),
                            category REFERENCES Category(designation),
                            regime REFERENCES Regimes(designation),
                            establishments REFERENCES Establishments(designation))
                            """)
        print "Tables DONE!"
        pass

    #commit data
    #param x - what to commit
    def commit(self, x):
        self.cursor.execute(x)
        self.connect.commit()

    #fill Institutions Table
    #@param year - year to search
    def fill_institutions_table(self, year):
        listType = self.data.do_corect_codes(self.data.get_TypeUniversity_Codes(year))
        for name, code in listType.iteritems():
            try:
                x = """ insert into institutions (designation, code) values ('{0}',{1})""".format(name, code)
                self.cursor.execute(x)
            except:
                pass
        self.connect.commit()
        print "Institutions Table DONE!"

    #fill establishment Table
    #@paran year - year to search
    def fill_establishment_table(self, year):
        listType = self.data.get_TypeUniversity_Codes(year)
        for type, typeCode in listType.iteritems():
            listUniv = self.data.get_NameUniversity_Codes(year, typeCode)
            for name, code in listUniv.iteritems():
                print year, "   ", name
                code = code[27:]
                try:
                    x = """ insert into establishments (designation, code) values ('{0}',{1})""".format(name, code)
                    self.cursor.execute(x)
                except:
                    pass
        self.connect.commit()
        print "Establishment Table DONE!"

    #fill Categories Table
    #@param year - year to search
    def fill_categories_table(self, year):
        listType = self.data.get_TypeUniversity_Codes(year)
        for type, typeCode in listType.iteritems():
            listUniv = self.data.get_NameUniversity_Codes(year, typeCode)
            for name, code in listUniv.iteritems():
                listHolders = self.data.get_teachers_information(year, type, code, name)
                code = code[27:]
                for codeTea, name, degree ,curse , category, regime, year, type, instituction in listHolders:
                    if(name != "&nbsp;"):
                        cutCode = '\&CodP\={0}'.format(code)
                        codeTea = re.sub(cutCode, '', codeTea)
                        print year, "   ", name
                        try:
                            x = """ insert into degrees (designation) values ('{0}')""".format(degree)
                            self.cursor.execute(x)
                        except:
                            pass
                        try:
                            x = """ insert into curses (designation) values ('{0}')""".format(curse)
                            self.cursor.execute(x)
                        except:
                            pass
                        try:
                            x = """ insert into category (designation) values ('{0}')""".format(category)
                            self.cursor.execute(x)
                        except:
                            pass
                        try: 
                            x = """ insert into regimes (designation) values ('{0}')""".format(regime)
                            self.cursor.execute(x)
                        except:
                            pass
                        try:
                            x = """ insert into teachers (name, code) values ('{0}', {1})""".format(name, codeTea)                           
                            self.cursor.execute(x)
                        except:
                            pass
        self.connect.commit()
        print "Categories DONE!"
        pass
    
    #fill the rest of the data for data base
    #param year - year to search
    def fill_rest(self, year):
        self.teacher = ""
        self.establishments = ""
        self.category = ""
        self.regime = ""
        self.typeUni = ""
        self.curse = ""
        self.degree = ""
        listType = self.data.get_TypeUniversity_Codes(year)
        for type, typeCode in listType.iteritems():
            listUniv = self.data.get_NameUniversity_Codes(year, typeCode)
            for name, code in listUniv.iteritems():
                listHolders = self.data.get_teachers_information(year, type, code, name)
                code = code[27:]
                for x in listHolders:
                    if x[1] != "&nbsp;":
                        print year, "   rest!   ",  x[1]
                        self.teacher = self.go_to_list(""" SELECT name FROM teachers WHERE name = '{0}'""".format(x[1]), self.teacher)
                        self.establishments = self.go_to_list(""" SELECT designation FROM establishments WHERE designation = '{0}'""".format(x[8]), self.establishments)
                        self.category = self.go_to_list(""" SELECT designation FROM category WHERE designation = '{0}'""".format(x[4]), self.category)
                        self.regime = self.go_to_list(""" SELECT designation FROM regimes WHERE designation = '{0}'""".format(x[5]), self.regime)
                        self.typeUni = self.go_to_list(""" SELECT designation FROM institutions WHERE designation = '{0}'""".format(x[7]), self.typeUni)
                        self.curse = self.go_to_list(""" SELECT designation FROM curses WHERE designation = '{0}'""".format(x[3]), self.curse)
                        self.degree = self.go_to_list(""" SELECT designation FROM degrees WHERE designation = '{0}'""".format(x[2]), self.degree)

                        x = """ INSERT INTO Teachers_Cards (teacher, year, institution, category, regime, establishments) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')""".format(self.teacher, x[6], self.typeUni, self.category, self.regime, self.establishments)
                        try:
                            self.cursor.execute(x)
                        except:
                            pass
                        
                        x = """ INSERT INTO Curse_Cards (year, teacher, degree, curse) VALUES ('{0}', '{1}', '{2}', '{3}')""".format(year, self.teacher, self.degree, self.curse)
                        try:
                            self.cursor.execute(x)
                        except:
                            pass
        self.connect.commit()

    #commit executa the list
    #@param commit - what to commit
    #@param x - empaty data for return after
    #@return x - return data
    def go_to_list(self, commit, x):
        try:
            self.cursor.execute(commit)
            r = self.cursor.fetchone()
            x = r[0].encode('utf-8')
        except:
            pass
        return x

### -------> HTTP Writer
    #write http page
    def write_http_page(self):
        self.file = open("index.html", "w")
        self.write_title()
        for i in range(0, 10):
            self.write_data_from_year(i)
        self.write_end()
        self.file.close()

    #Write all title from http server    
    def write_title(self):
        self.file.write('<!doctype html><html><head><title>Rebides Server</title><style type="text/css">')
        self.file.write('h1, h2, h3, h4, h5, h6, h7, h8, h9, h10, h11{color:blue;}h1, h2, h3, h4, h5, h6, h7, h8, h9, h10, h11')
        self.file.write('{text-align:center;}</style></head><body><h1>REBIDES SERVER</h1>')
        self.file.write('<A HREF="#2000">2000</a><br /><A HREF="#2001">2001</a><br /><A HREF="#2002">2002</a><br />')
        self.file.write('<A HREF="#2003">2003</a><br /><A HREF="#2004">2004</a><br /><A HREF="#2005">2005</a><br />')
        self.file.write('<A HREF="#2006">2006</a><br /><A HREF="#2007">2007</a><br /><A HREF="#2008">2008</a><br /><A HREF="#2009">2009</a><br />')

    #write data by year
    #@param year - year to search
    def write_data_from_year(self, year):
        self.file.write('<a name="200{0}"></a>'.format(year))
        self.file.write("<h{0}>200{1}</h{0}>".format(year + 2, year))
        self.file.write("<p><strong>LISTS... </strong><br />")
        self.file.write('Institutions... <br />')
        self.stat = GetStatistics()
        list = self.stat.get_lists('institution', year)
        self.write_paragraf(list)
        self.file.write("</p><hr />")
        print "institutions DONE!", "Year: 200{0}".format(year)

        self.stat = GetStatistics()
        self.file.write('<p><strong>Establishments...</strong> <br />') 
        list = self.stat.get_lists('establishments', 0)
        self.write_paragraf(list)
        self.file.write("</p><hr />")
        print "Establishments DONE!", "Year: 200{0}".format(year)

        self.stat = GetStatistics()
        self.file.write('<p><strong>Holders of Degree...</strong> <br />' )
        list = self.stat.get_lists('teacher', 0)
        self.write_paragraf(list)
        self.file.write("</p><hr />")
        print "Holders List DONE!", "Year: 200{0}".format(year)
        
        self.write_tables(year)

    #write tables whit statistics
    #@param year - year to search
    def write_tables(self, year):
        self.stat = GetStatistics()
        self.file.write('<table border="1"><tr><th colspan="2">ESTATISTICS... </th></tr>')
        self.file.write('<tr><th>Teachers</th><th>Number of Teachers in 200{0}</th></tr>'.format(year))
        list = self.stat.get_num_teachers_year(year)
        self.file.write('<tr><td>Teachers</td><td>{0}</td></tr>'.format(list))

        self.stat = GetStatistics()
        self.file.write('<tr><th>Institution</th><th>Number of Teachers in 200{0}</th></tr>'.format(year))
        list = self.stat.get_num_teachers_anywhere_per_year('institution', 'teachers_cards', year)
        self.write_on_table(list, False)

        self.stat = GetStatistics()
        self.file.write('<tr><th>Establishments</th><th>Number of Teachers in 200{0}</th></tr>'.format(year))
        list = self.stat.get_num_teachers_anywhere_per_year('establishments', 'teachers_cards', year)
        self.write_on_table(list, False)

        self.stat = GetStatistics()
        self.file.write('<tr><th>Degree</th><th>Number of Teachers in 200{0}</th></tr>'.format(year))
        list = self.stat.get_num_teachers_anywhere_per_year('degree', 'curse_cards', year)
        self.write_on_table(list, False)

        self.stat = GetStatistics()
        self.file.write('<tr><th>Establishments, Degree</th><th>Number of Teachers in 200{0}</th></tr>'.format(year))
        list = self.stat.get_num_teachers_degree_establishment_year(year)
        self.write_on_table(list, True)
        self.file.write("</table>")

    #to write on table, he wrotes every sigle line
    #@param dict - dictinair to print on http page
    #@param bool - if as or not 2 elements on key element
    def write_on_table(self, dict, bool):
        if bool == True:
            for x, y in dict.iteritems():
                self.file.write("<tr><td>'{0}', '{1}'</td><td>{2}</td></tr>".format(x[0].encode('utf8'), x[1].encode('utf8'), y))
        else:
            for x, y in dict.iteritems():
                self.file.write("<tr><td>'{0}'</td><td>{1}</td></tr>".format(x.encode('utf8'), y))

    #write all lines oon list
    #@param list - list to print on http page
    def write_paragraf(self, list):
        for x in list:
                self.file.write(x.encode('utf8') + "<br />")

    #end rhe http page
    def write_end(self):
        self.file.write("</body></html>")
